#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection

from web.dao.base_dao import BaseDao
from web.models.quant2_commodity_future_filter import Quant2CommodityFutureFilter

class Quant2CommodityFutureFilterDao(BaseDao):
    """
    Quant2CommodityFutureFilter的dao类
    """

    model_class = Quant2CommodityFutureFilter

    def truncate_table(self):
        """
        清空表
        """

        with connection.cursor() as cursor:
            cursor.execute("TRUNCATE TABLE quant2_c_f_filter")

    def insert(self, quant2_commodity_future_filter_list: list):
        """
        批量插入记录
        """

        self.save_batch(quant2_commodity_future_filter_list)

    def find_all(self):
        """
        查询所有记录
        """

        return self.find_list(dict(), dict(), list())

    def find_distinct_code(self) -> list[str]:
        """
        查询code，并去重
        """

        with connection.cursor() as cursor:
            cursor.execute("select distinct t.code from quant2_c_f_filter t")
            code_tuple_list = cursor.fetchall()
            return [_tuple[0] for _tuple in code_tuple_list]

    def delete_not_main_contract_by_code_list(self, main_contract_code_list: list[str]):
        """
        根据code列表，删除非主力合约
        """
        quant2_commodity_future_filter_list: list[Quant2CommodityFutureFilter] = self.find_list(dict(), dict(), dict())
        for quant2_commodity_future_filter in quant2_commodity_future_filter_list:
            if quant2_commodity_future_filter.code in main_contract_code_list:
                continue
            else:
                filter_dict = {"code": quant2_commodity_future_filter.code}
                self.delete_batch_by_query(filter_dict, dict())

    def find_distinct_commodity_future_info_code_in_code(self):
        """
        从code字段中提取commodity_future_info表中code字段类型的字符串，并去重
        """

        with connection.cursor() as cursor:
            cursor.execute("SELECT distinct REGEXP_SUBSTR(SUBSTR(t.code, INSTR(t.code, '.', 1) + 1), '[A-Z|a-z]+', 1) FROM quant2_c_f_filter t")
            code_tuple_list = cursor.fetchall()
            return [_tuple[0] for _tuple in code_tuple_list]

    def order_by_up_down_percentage_with_n_date_desc(self) -> list[Quant2CommodityFutureFilter]:
        """
        查询quant2_c_f_filter表的记录，不包括已经持仓的记录，，并按照up_down_percentage_with_n_date列降序排列。然后创建升序数组
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.* from quant2_c_f_filter t where t.code not in("
                           "select distinct t1.code from quant2_c_f_transact_record t1 where ((t1.direction=1 and t1.sell_date is null) or (t1.direction=-1 and t1.buy_date is null))) "
                           "order by t.up_down_percentage_with_n_date desc")
            tuple_list = cursor.fetchall()
            if tuple_list is not None and len(tuple_list) > 0:
                quant2_commodity_future_filter_list: list[Quant2CommodityFutureFilter] = list()
                for _tuple in tuple_list:
                    quant2_commodity_future_filter: Quant2CommodityFutureFilter = Quant2CommodityFutureFilter()
                    quant2_commodity_future_filter.id = _tuple[0]
                    quant2_commodity_future_filter.code = _tuple[1]
                    quant2_commodity_future_filter.turnover = _tuple[2]
                    quant2_commodity_future_filter.up_down_percentage_with_n_date = _tuple[3]
                    quant2_commodity_future_filter.normalization_variance120 = _tuple[4]
                    quant2_commodity_future_filter_list.append(quant2_commodity_future_filter)
                return quant2_commodity_future_filter_list
            else:
                return None

    def find_by_not_close_position_order_by_up_down_percentage_with_n_date_desc(self) -> list[Quant2CommodityFutureFilter]:
        """
        查询quant2_c_f_filter表的记录，不包括quant2_c_f_transact_record表中还没有平仓的记录，并按照up_down_percentage_with_n_date列降序排列
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.* from quant2_c_f_filter t where t.code not in(" \
				"select t1.code from quant2_c_f_transact_record t1 " \
				"where (t1.direction=1 and t1.sell_date is null and t1.sell_price is null and t1.sell_lot is null) or "
                "(t1.direction=-1 and t1.buy_date is null and t1.buy_price is null and t1.buy_lot is null)) "
                "order by t.up_down_percentage_with_n_date desc")
            tuple_list = cursor.fetchall()
            if tuple_list is not None and len(tuple_list) > 0:
                quant2_commodity_future_filter_list: list[Quant2CommodityFutureFilter] = list()
                for _tuple in tuple_list:
                    quant2_commodity_future_filter: Quant2CommodityFutureFilter = Quant2CommodityFutureFilter()
                    quant2_commodity_future_filter.id = _tuple[0]
                    quant2_commodity_future_filter.code = _tuple[1]
                    quant2_commodity_future_filter.turnover = _tuple[2]
                    quant2_commodity_future_filter.up_down_percentage_with_n_date = _tuple[3]
                    quant2_commodity_future_filter.normalization_variance120 = _tuple[4]
                    quant2_commodity_future_filter_list.append(quant2_commodity_future_filter)
                return quant2_commodity_future_filter_list
            else:
                return None

    def find_by_not_close_position_order_by_normalization_variance120_desc(self) -> list[Quant2CommodityFutureFilter]:
        """
        查询quant2_c_f_filter表的记录，不包括quant2_c_f_transact_record表中还没有平仓的记录，并按照normalization_variance120列降序排列
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.* from quant2_c_f_filter t where t.code not in(" \
                           "select t1.code from quant2_c_f_transact_record t1 " \
                           "where (t1.direction=1 and t1.sell_date is null and t1.sell_price is null and t1.sell_lot is null) or "
                           "(t1.direction=-1 and t1.buy_date is null and t1.buy_price is null and t1.buy_lot is null)) "
                           "order by t.normalization_variance120 desc")
            tuple_list = cursor.fetchall()
            if tuple_list is not None and len(tuple_list) > 0:
                quant2_commodity_future_filter_list: list[Quant2CommodityFutureFilter] = list()
                for _tuple in tuple_list:
                    quant2_commodity_future_filter: Quant2CommodityFutureFilter = Quant2CommodityFutureFilter()
                    quant2_commodity_future_filter.id = _tuple[0]
                    quant2_commodity_future_filter.code = _tuple[1]
                    quant2_commodity_future_filter.turnover = _tuple[2]
                    quant2_commodity_future_filter.up_down_percentage_with_n_date = _tuple[3]
                    quant2_commodity_future_filter.normalization_variance120 = _tuple[4]
                    quant2_commodity_future_filter_list.append(quant2_commodity_future_filter)
                return quant2_commodity_future_filter_list
            else:
                return None

    def page_with_order(self, asc: bool, page_no: int, page_size: int) -> list[Quant2CommodityFutureFilter]:
        """
        可以按照升序或者降序，分页查询
        """

        with connection.cursor() as cursor:
            sql: str = None
            if asc is True:
                sql = "select * from (select ROW_NUMBER() OVER (ORDER BY up_down_percentage_with_n_date asc) rownumber, t.* from quant2_c_f_filter t order by up_down_percentage_with_n_date asc) where rownumber between ({}-1)*{}+1 and {}*{}".format(
                    page_no, page_size, page_no, page_size)
            if asc is False:
                sql = "select * from (select ROW_NUMBER() OVER (ORDER BY up_down_percentage_with_n_date desc) rownumber, t.* from quant2_c_f_filter t order by up_down_percentage_with_n_date desc) where rownumber between ({}-1)*{}+1 and {}*{}".format(
                    page_no, page_size, page_no, page_size)
            cursor.execute(sql)
            tuple_list = cursor.fetchall()
            if tuple_list is not None and len(tuple_list) > 0:
                quant2_commodity_future_filter_list: list[Quant2CommodityFutureFilter] = list()
                for _tuple in tuple_list:
                    quant2_commodity_future_filter: Quant2CommodityFutureFilter = Quant2CommodityFutureFilter()
                    quant2_commodity_future_filter.id = _tuple[1]
                    quant2_commodity_future_filter.code = _tuple[2]
                    quant2_commodity_future_filter.turnover = _tuple[3]
                    quant2_commodity_future_filter.up_down_percentage_with_n_date = _tuple[4]
                    quant2_commodity_future_filter.normalization_variance120 = _tuple[5]
                    quant2_commodity_future_filter_list.append(quant2_commodity_future_filter)
                return quant2_commodity_future_filter_list
            else:
                return None

    def find_current_and_max_and_min_variance20(self, code: str, transaction_date: str):
        """
        根据code，查询当前的variance120、最大的variance120和最小的variance120
        """

        with connection.cursor() as cursor:
            current_variance120: float = None
            max_variance120: float = None
            min_variance120: float = None

            # 查询当前variance120
            sql: str = "select t.variance120 from c_f_date_contract_data t where t.code='{}' and t.transaction_date=to_date({},'yyyy-mm-dd')".format(
                    code, transaction_date)
            cursor.execute(sql)
            tuple_list = cursor.fetchone()
            if tuple_list is not None and len(tuple_list) > 0:
                current_variance120 = tuple_list[0]

            # 查询最大的variance120和最小的variance120
            sql = "select max(t.variance120), min(t.variance120) from c_f_date_contract_data t where t.code='{}'".format(code)
            cursor.execute(sql)
            tuple_list = cursor.fetchone()
            if tuple_list is not None and len(tuple_list) > 0:
                max_variance120 = tuple_list[0]
                min_variance120 = tuple_list[1]

            return current_variance120, max_variance120, min_variance120